﻿-- ================================================
--作者:马双全 
--创建日期:   2012年7月24日
--修改日期：
--版本号：　V1.0
--描述:  分页查询 彩种信息
--所属数据库:PlayMessage
--备注: 

-- 
--declare @TotalCount int EXEC [proc_GetPlaysShowsInfoPageList_Select] @SortFieldName='1',@TotalCount =@TotalCount  out
--drop proc proc_GetPlaysShowsInfoPageList_Select
-- ================================================

CREATE PROCEDURE [host8446263].[proc_GetPlaysShowsInfoPageList_Select]
	@PageSize int=15,
    @PageIndex int=1,
    @SortFieldName nvarchar(50), --排序字段
    @SortDirection INT=0,	         --排序方向,
    @LotteryID int=null    ---彩种类型
	--@TotalCount int output
AS
	
	declare @sql nvarchar(max), @SortFieldNameSortDirection nvarchar(50);
	set @SortFieldNameSortDirection=isnull(@SortFieldName,'ID')+ltrim(@SortDirection);
	set @sql=
	'SELECT  * FROM 
		(SELECT 
		     Id, LotteryID, PlayNo, PlayName, StartTime, EndTime, WinLotteryNumber,
		     StateUpdatetime, PlayState, CreateTime,
			case '''+@SortFieldNameSortDirection+'''
				 		WHEN ''CreateTime0'' THEN ROW_NUMBER() OVER (ORDER BY  ps.CreateTime DESC) 
						WHEN ''CreateTime1'' THEN ROW_NUMBER() OVER (ORDER BY  ps.CreateTime) 
						WHEN ''ID0'' THEN ROW_NUMBER() OVER (ORDER BY  ps.ID DESC) 
						WHEN ''ID1'' THEN ROW_NUMBER() OVER (ORDER BY  ps.ID) 
						ELSE  ROW_NUMBER() OVER (ORDER BY  ps.ID DESC)
						END AS Rn

		 FROM pm_plays ps
		 WHERE (@LotteryID is null or ps.LotteryID=@LotteryID)
		 ) 
		as r
	 WHERE r.Rn between (@PageIndex-1)*@PageSize+1 and @PageIndex*@PageSize;
	 '
PRINT @sql

	exec sp_executesql @sql,N'@PageSize int, @PageIndex int,@LotteryID int',
	 @PageSize,@PageIndex,@LotteryID
GO

